package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.constants.LogConstants;
import cn.com.checne.model.domain.LogInfo;
import cn.com.checne.model.domain.NavigateInfo;
import cn.com.checne.model.domain.UserInfo;
import cn.com.checne.model.dto.LogInfoQuery;
import cn.com.checne.util.TimeUtils;

@Repository
public class LogInfoDao {
	protected static final Logger log = Logger.getLogger(LogInfoDao.class);

	/**
	 * create
	 * 
	 * @param cate
	 * @return
	 */
	public boolean create(LogInfo log) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_log(log_userid_fk,log_action_id,log_navi_id_fk,log_desc,log_time)"
					+ " values(?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0;
			pstmt.setInt(++i, log.getUserId());
			pstmt.setInt(++i, log.getLogActionId());
			pstmt.setInt(++i, log.getNaviId());
			pstmt.setString(++i, log.getLogDesc());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(log.getLogTime()
					.getTime()));
			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}
	public boolean create(int userId,int actionId,int naviId,String desc) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_log(log_userid_fk,log_action_id,log_navi_id_fk,log_desc,log_time)"
					+ " values(?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0;
			pstmt.setInt(++i, userId);
			pstmt.setInt(++i, actionId);
			pstmt.setInt(++i, naviId);
			pstmt.setString(++i, desc);
			pstmt.setTimestamp(++i, new java.sql.Timestamp(new java.util.Date().getTime()));
			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}
	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_log where log_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0;
			pstmt.setInt(++i, id);
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}

	/**
	 * 
	 * @param query
	 * @param start
	 * @param num
	 * @return
	 */
	public List<LogInfo> getLogListByCondition(LogInfoQuery query, int start,
			int num) {
		List<LogInfo> list = new ArrayList<LogInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_log where 1=1 ";
			if (query.getUserId() > 0) {
				sql += " and log_userid_fk = ? ";
			}
			if (query.getActionId() > 0) {
				sql += " and log_action_id = ? ";
			}
			if (query.getNaviId() > 0) {
				sql += " and log_navi_id_fk = ? ";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and log_time >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and log_time <= ? ";
			}
			sql += " order by log_time desc  limit ?,? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			if (query.getUserId() > 0) {
				pstmt.setInt(++i, query.getUserId());
			}
			if (query.getActionId() > 0) {
				pstmt.setInt(++i, query.getActionId());
			}
			if (query.getNaviId() > 0) {
				pstmt.setInt(++i, query.getNaviId());
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 
	 * @param query
	 * @return
	 */
	public int getLogCountByCondition(LogInfoQuery query) {
		int total = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_log where 1=1 ";
			if (query.getUserId() > 0) {
				sql += " and log_userid_fk = ? ";
			}
			if (query.getActionId() > 0) {
				sql += " and log_action_id = ? ";
			}
			if (query.getNaviId() > 0) {
				sql += " and log_navi_id_fk = ? ";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and log_time >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and log_time <= ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			if (query.getUserId() > 0) {
				pstmt.setInt(++i, query.getUserId());
			}
			if (query.getActionId() > 0) {
				pstmt.setInt(++i, query.getActionId());
			}
			if (query.getNaviId() > 0) {
				pstmt.setInt(++i, query.getNaviId());
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}


	/**
	 * get rseult from resultSet
	 * 
	 * @param rs
	 * @return
	 */
	public List<LogInfo> getResult(ResultSet rs) {
		List<LogInfo> list = new ArrayList<LogInfo>();
		try {
			LogInfo log = null;
			while (rs.next()) {
				log = new LogInfo();
				log.setLogId(rs.getInt("log_id"));
				log.setLogDesc(rs.getString("log_desc"));
				log.setLogTime(rs.getTimestamp("log_time"));

				log.setUserId(rs.getInt("log_userid_fk"));
				UserInfo userInfo = new UserInfoDao().getUser(log.getUserId());
				log.setUserInfo(userInfo);

				log.setNaviId(rs.getInt("log_navi_id_fk"));
				NavigateInfo navigateInfo = new NavigateInfoDao()
						.getNavigate(log.getNaviId());
				log.setNavigateInfo(navigateInfo);
				
				log.setLogActionId(rs.getInt("log_action_id"));
				log.setLogActionName(LogConstants.LOGACTIONMAP.get(log.getLogActionId()));

				list.add(log);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}